package com.yenlien.traceyun.plant.dao;


import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.dock_device.xinfengxiang.XinFengXiang;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;

/**
 * @author jialong
 * @date 2021/03/31
 * @description: 新风向数据实体
 **/

public interface XinFengXiangDao extends BaseJpaDao<XinFengXiang> {

    /**
     * 根据地块ID查询数据
     *
     * @param baseSoilId
     * @return
     */
    List<XinFengXiang> findByBaseSoilId(String baseSoilId);


    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(date_time) ctime, e_name paramName, ANY_VALUE(e_unit) paramUnit, ANY_VALUE(e_value) paramVal, " +
            "ANY_VALUE(company_id) companyId, ANY_VALUE(equipment_id) equipmentId, ANY_VALUE(base_soil_id) baseSoilId, " +
            "ANY_VALUE(equipment_name) equipmentName FROM" +
            "(SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1 HAVING 1 ORDER BY date_time DESC) t GROUP BY t.e_name", nativeQuery = true)
    List<JSONObject> getLast(String devId);




    @Query(value = "SELECT  id, date_time ctime, e_name paramName, e_unit paramUnit, e_value paramVal, " +
            "company_id companyId, equipment_id equipmentId, base_soil_id baseSoilId, equipment_name equipmentName " +
            "FROM t_xin_feng_xiang WHERE equipment_id = ?1 ORDER BY create_time DESC LIMIT ?2", nativeQuery = true)
    List<JSONObject> getLast(String equId, Integer limit);


    @Query(value = "SELECT e_value paramVal, date_time cTime FROM t_xin_feng_xiang WHERE date_format(date_time, '%Y-%m') = ?1 AND e_name = ?2 AND company_id = ?3", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName, String fType);


    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1 AND date_time BETWEEN ?2 AND ?3 ORDER BY date_time DESC", nativeQuery = true)
    List<XinFengXiang> getHistoryData(String devId, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1 AND e_name = ?2 AND date_time BETWEEN ?3 AND ?4 ORDER BY date_time DESC", nativeQuery = true)
    List<XinFengXiang> getHistoryData(String devId, String paramName, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1  ORDER BY date_time DESC", nativeQuery = true)
    List<XinFengXiang> getHistoryData(String devId);


    @Query(value = "SELECT e_name paramName, MAX(e_value) max, MIN(e_value) min, ROUND(AVG(e_value), 2) avg FROM t_xin_feng_xiang WHERE date_format(date_time, '%Y-%m') = ?1 AND company_id = ?2 GROUP BY e_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String fType);

    @Query(value = "SELECT e_name paramName, ANY_VALUE(e_unit) paramUnit FROM t_xin_feng_xiang WHERE equipment_id = ?1 GROUP BY e_name", nativeQuery = true)
    List<JSONObject> getParamNameList(String equId);

    @Query(value = "SELECT e_value paramVal, e_name paramName, date_time cTime, equipment_id devId FROM t_xin_feng_xiang WHERE date_format(date_time, '%Y-%m') = ?1 AND e_name = ?2 AND equipment_id = ?3", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String paramName, String equId);


    @Query(value = "SELECT e_value paramVal, e_name paramName, date_time cTime, equipment_id devId, e_unit unit FROM t_xin_feng_xiang WHERE date_format(date_time, '%Y-%m') = ?1 AND equipment_id = ?2 ORDER BY date_time", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String equId);


    @Query(value = "SELECT GROUP_CONCAT(CONCAT(e_name, '=', e_value)) FROM t_xin_feng_xiang WHERE create_time IS NOT NULL AND equipment_id = ?1 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId);

    @Query(value = "SELECT GROUP_CONCAT(CONCAT(e_name, '=', e_value)) FROM t_xin_feng_xiang WHERE create_time IS NOT NULL AND equipment_id = ?1 AND create_time BETWEEN ?2 AND ?3 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId, String startTime, String endTime);


    @Query(value = "SELECT e_value paramVal, date_time `time` FROM t_xin_feng_xiang WHERE equipment_id = ?1 AND e_name = ?2", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName);

    @Query(value = "SELECT e_value paramVal, date_time `time` FROM t_xin_feng_xiang WHERE equipment_id = ?1 AND e_name = ?2 AND date_time BETWEEN ?3 AND ?4", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName, String startTime, String endTime);



    @Query(value = "SELECT e_name paramName, MAX(e_value) max, MIN(e_value) min, ROUND(AVG(e_value), 2) avg FROM t_xin_feng_xiang " +
            "WHERE date_format(date_time, '%Y-%m') = ?1 AND company_id = ?2 AND equipment_id = ?3 GROUP BY e_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String fType, String equId);

    @Query(value = "SELECT e_name paramName, MAX(e_value) max, MIN(e_value) min, ROUND(AVG(e_value), 2) avg FROM t_xin_feng_xiang " +
            "WHERE date_format(date_time, '%Y-%m-%d') = ?1 AND company_id = ?2 AND equipment_id = ?3 GROUP BY e_name ", nativeQuery = true)
    List<JSONObject> getAnalysisDay(String yearMonth, String fType, String equId);

    @Query(value = "SELECT e_name paramName, MAX(e_value) max, MIN(e_value) min, ROUND(AVG(e_value), 2) avg FROM t_xin_feng_xiang " +
            "WHERE date_format(date_time, '%Y-%m') = ?1 AND equipment_id = ?2 AND e_name = ?3 GROUP BY e_name ", nativeQuery = true)
    JSONObject getAnalysisName(String yearMonth, String equId, String paramName);


    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE company_id = ?1 AND TO_DAYS(date_time) = TO_DAYS(?2) LIMIT 10", nativeQuery = true)
    List<XinFengXiang> findByCompAndTime(String companyId, LocalDate time);

    @Query(value = "SELECT t.e_name, ANY_VALUE(t.e_value) e_value FROM (SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1 HAVING 1 ORDER BY date_time DESC) t GROUP BY t.e_name", nativeQuery = true)
    List<JSONObject> dock(String equId);

    /**
     * 根据设备ID查询数据
     *
     * @param equipmentId
     * @return
     */
    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1", nativeQuery = true)
    List<XinFengXiang> findByEquipmentId(String equipmentId);

    @Query(value = "SELECT * FROM t_xin_feng_xiang WHERE equipment_id = ?1 AND create_time BETWEEN ?2 AND ?3", nativeQuery = true)
    List<XinFengXiang> findHistoryData(String equId, Date startTime, Date endTime);


    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(device_id) device_id, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name,ANY_VALUE(date_time) date_time, ANY_VALUE(e_unit) e_unit,ANY_VALUE(e_value) e_value," +
            "ANY_VALUE(e_key) e_key, ANY_VALUE(e_name) e_name, ANY_VALUE(e_num) e_num, ANY_VALUE(create_time) create_time FROM t_xin_feng_xiang " +
            "WHERE base_soil_id = ?1 AND TO_DAYS(NOW()) = TO_DAYS(date_time) GROUP BY e_name" +
            " UNION " +
            "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(device_id) device_id, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name,ANY_VALUE(date_time) date_time, ANY_VALUE(e_unit) e_unit,ANY_VALUE(e_value) e_value," +
            "ANY_VALUE(e_key) e_key, ANY_VALUE(e_name) e_name, ANY_VALUE(e_num) e_num, ANY_VALUE(create_time) create_time FROM t_xin_feng_xiang " +
            "WHERE base_soil_id = ?1 AND TO_DAYS(DATE_SUB(NOW(), INTERVAL 1 DAY)) = TO_DAYS(date_time) GROUP BY e_name" +
            " UNION " +
            "SELECT ANY_VALUE(id) id, ANY_VALUE(equipment_id) equipment_id, ANY_VALUE(device_id) device_id, ANY_VALUE(base_soil_id) base_soil_id, " +
            "ANY_VALUE(company_id) company_id, ANY_VALUE(equipment_name) equipment_name,ANY_VALUE(date_time) date_time, ANY_VALUE(e_unit) e_unit,ANY_VALUE(e_value) e_value," +
            "ANY_VALUE(e_key) e_key, ANY_VALUE(e_name) e_name, ANY_VALUE(e_num) e_num, ANY_VALUE(create_time) create_time FROM t_xin_feng_xiang " +
            "WHERE base_soil_id = ?1 AND TO_DAYS(DATE_SUB(NOW(), INTERVAL 2 DAY)) = TO_DAYS(date_time) GROUP BY e_name"
            , nativeQuery = true)
    List<XinFengXiang> findList3Days(String baseSoilId);


}
